USE [LIWMS]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_IndexMaintenance]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_IndexMaintenance]
GO
USE [LIWMS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_IndexMaintenance] @DBName SYSNAME
AS
BEGIN
  DECLARE @TableName SYSNAME, @SchemaName SYSNAME, @IndexName SYSNAME, @Frag FLOAT, @object_id INT, @index_id INT, @PartitionCount INT
  DECLARE @IndexTable TABLE(TableName SYSNAME,SchemaName SYSNAME,IndexName SYSNAME,Frag FLOAT,object_id INT,index_id INT)
  DECLARE @query NVARCHAR(MAX) = REPLACE('
  USE #DBName#
  DECLARE @DBId INT
  SET @DBId = DB_ID(N''#DBName#'')
  SELECT o.name AS ObjectName, s.name AS SchemaName, si.name AS IndexName,
         dm.avg_fragmentation_in_percent AS Frag,dm.object_id AS objectid, dm.index_id AS indexid
  FROM master.sys.dm_db_index_physical_stats (@DBId, NULL, NULL , NULL, ''LIMITED'') AS dm
  INNER JOIN sys.objects AS o
  ON dm.object_id = o.object_id
  INNER JOIN sys.schemas as s 
  ON s.schema_id = o.schema_id
  INNER JOIN sys.indexes AS si
  ON si.object_id = dm.object_id AND si.index_id = dm.index_id
  WHERE dm.avg_fragmentation_in_percent > 10.0 AND dm.index_id > 0','#DBName#',@DBName)
  DECLARE @parm NVARCHAR(MAX) = '@objectid INT,@indexid INT,@Num INT OUTPUT'
  INSERT INTO @IndexTable
  EXEC SP_EXECUTESQL @query
  DECLARE IndexCur CURSOR
  FOR
    SELECT TableName,SchemaName,IndexName,Frag,object_id,index_id
    FROM @IndexTable
    ORDER BY TableName,Frag
  OPEN IndexCur
  FETCH NEXT FROM IndexCur INTO @TableName,@SchemaName,@IndexName,@Frag,@object_id,@index_id
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @query = REPLACE('
    USE #DBName#
    SELECT @Num = COUNT(*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid',
    '#DBName#',@DBName)
    EXEC SP_EXECUTESQL @query,@parm,@objectid = @object_id,@indexid = @index_id,@Num = @PartitionCount OUTPUT
    
    SET @query = REPLACE('
    USE #DBName#
    ALTER INDEX #Index# ON #Schema#.#Table# #Method# #Part#','#DBName#',@DBName)
    SET @query = REPLACE(@query,'#Index#',@IndexName)
    SET @query = REPLACE(@query,'#Schema#',@SchemaName)
    SET @query = REPLACE(@query,'#Table#',@TableName)
    SET @query = REPLACE(@query,'#Method#',
    CASE WHEN @Frag < 30.0 THEN 'REORGANIZE' WHEN @Frag >= 30.0 THEN 'REBUILD' END)
    SET @query = REPLACE(@query,'#Part#',
    CASE WHEN @PartitionCount > 1 THEN 'PARTITION = ' + CAST(@PartitionCount AS NVARCHAR(10)) ELSE '' END) 
    
    --PRINT @query 
    EXEC SP_EXECUTESQL @query
                                                
    FETCH NEXT FROM IndexCur INTO @TableName,@SchemaName,@IndexName,@Frag,@object_id,@index_id
  END
  CLOSE IndexCur
  DEALLOCATE IndexCur
END


GO


